// 本模块是为了能跨模块统一和公用对同一数据表查询的加工结果，例如crm.js和api.js都可以使用

const moment = require("moment");
const rds = require("./rds");
const tutils = require("./tutils");

//注意这里的asker需要用req.session.authUserInfo
async function checkUserPermission(targetId, asker, permission) {
    const role = asker.crmROLE;

    if (role == "") return false;
    if (role == "crm-admin" || role == "crm-service") return true;

    const query = ` SELECT u.uid, u.rep_id, u.spread_uid as cid, c.rep_code as crep, c.rep_id  as crep_id
        FROM tutem_mall.eb_user u LEFT JOIN tutem_mall.eb_user c on (u.spread_uid = c.uid) WHERE u.uid = ${targetId} `;

    const [records] = await rds.query(query);
    target = records[0];
    if (!target) return false;

    if (permission == "crud") {
        //其它情况都要求asker具有有效的repID
        if (asker.repID != 0 && target.rep_id == asker.repID) return true;
        if (asker.repID != 0 && target.crep_id == asker.repID) return true;
        return false;
    }

    if (permission == "read") {
        if (
            role == "crm-gmoffc" ||
            role == "crm-advisor" ||
            role == "crm-mkting"
        )
            return true;
        if (asker.repID != 0 && target.rep_id == asker.repID) return true;
        if (asker.repID != 0 && target.crep_id == asker.repID) return true;
        return false;
    }

    return false;
}

async function checkOrderPermission(targetId, asker, permission) {
    return false;
}

async function checkTaskPermission(targetId, asker, permission) {
    return false;
}

// eb_user_profile表的公用用户档案查询函数
async function queryUserProfiles(query, params = [], role = "") {
    const [rows] = await rds.query(query, params);
    return rows.map((row) => {
        let { confidentiality, attachment_urls, memo, add_time } = row;

        if (confidentiality == "Unclassified") memo = "🌐" + memo;
        if (confidentiality == "Confidential") memo = "ℹ️" + memo;

        if (confidentiality == "Secret" || confidentiality == "TopSecret") {
            memo =
                confidentiality == "TopSecret"
                    ? "🔒**********"
                    : "🛡️" + tutils.maskString(memo, "shuffle");
            attachment_urls = `["https://totemlife.cn/apps/public/confidential.png"]`;
        }
        let urls = [];
        if (attachment_urls.startsWith("[") && attachment_urls.endsWith("]")) {
            try {
                urls = JSON.parse(attachment_urls);
            } catch (error) {
                urls = []; // 如果 JSON 解析出错，将 urls 设置为空数组
            }
        }
        const attachments_link =
            urls && urls.length > 0
                ? urls
                      .map(
                          (url, index) =>
                              `<a href="${url}" target="_blank">附件-${index + 1}</a> `
                      )
                      .join("")
                : "";
        const created_at = moment.unix(add_time).format("YYYY-MM-DD HH:mm:ss");
        const profile = {
            ...row,
            memo,
            attachment_urls,
            attachments_link,
            created_at,
        };
        return profile;
    });
}

// eb_user
async function getUser(uid) {
    const query = `
        SELECT u.*,
          u.spread_uid as cid, c.rep_code as crep, c.rep_id as crep_id, c.real_name as cname, c.promoter_type as ctype, c.is_promoter as cpromoter, c.agency as cagency, c.department as cdep,
          COALESCE(r.id, 0) as is_rep_id, COALESCE(r.code, '') as is_rep_code, COALESCE(r.team, '') as is_rep_team,
          COALESCE(r.id, 0) as repID, COALESCE(r.code, '') as repCODE, COALESCE(r.team, '') as repTEAM,
          w.subscribe, w.headimgurl,
          f.memo as last_memo, f.confidentiality as memo_confidentiality, f.add_time as memo_time
        FROM tutem_mall.eb_user u
          LEFT JOIN tutem_mall.eb_user c on (u.spread_uid = c.uid)
          LEFT JOIN tutem_mall.eb_rep r on (u.uid = r.uid)
          LEFT JOIN tutem_mall.eb_wechat_user w on (w.uid = u.uid)
          LEFT JOIN ( SELECT *, ROW_NUMBER() OVER(PARTITION BY uid ORDER BY id DESC) AS row_num FROM tutem_mall.eb_user_profile) f on (u.uid = f.uid and f.row_num = 1)
        WHERE u.uid = ${uid}
    `;

    const [users] = await rds.query(query);
    let user = users[0];
    if (!user) return null;

    const [levels] = await rds.query(
        `select * from tutem_mall.eb_user_level where uid = ${uid} and status = 1 and is_del = 0 order by discount asc`
    );
    const level = levels[0];
    const discount = level
        ? level.discount !== undefined
            ? level.discount
            : 100
        : 100;

    const avatar =
        user.headimgurl ||
        user.avatar ||
        "https://totemlife.cn/apps/public/weapp-cover-300x300.png";

    const memberInfo =
        user.status == 0 ? '<span title="高危用户">👿</span> ' : " ";
    const subscribeInfo =
        user.subscribe == 0
            ? '<span title="未关注公号"><i class="fa-regular fa-bell-slash"></i></span> '
            : '<span title="已关注公号"><i class="fa-regular fa-bell"></i></span> ';
    const promoterInfo =
        user.is_promoter == 1 ? `<span title="推广类型：P1">📢</span> ` : " ";

    const typeInfo =
        user.promoter_type > 0
            ? `<span title="会员类型：T${user.promoter_type}">${tutils.getEmojiedUserType(user.promoter_type)}</span> `
            : " ";
    const role = tutils.getCrmRole(user.roles);
    const roleInfo = `<span title="系统角色：${role.toUpperCase()}">${tutils.getEmojiedUserRole(role)}</span> `;
    const sourceInfo =
        user.login_type == "mta"
            ? `<span title="MTA首荐"><i class="fa-solid fa-shield-halved"></i></span>!`
            : '<span title="小程序首荐"><i class="fa-solid fa-shield-halved"></i></span>';
    const codes = `
        <span title="${user.cid === 0 || !user.cname ? "无上级推荐人" : user.cname + " " || ""}${user.cagency || ""} ${user.cdep || ""} ${user.crep || ""}">
            C${user.cid} ${user.cpromoter === 1 ? '<i class="fa fa-rss"></i>' : ""}
        </span>
        <span>
            A${user.cs_aid}R${user.rep_id}${user.rep_id > 0 ? "-" + user.rep_code : ""} P${user.is_promoter}T${user.promoter_type} L${user.level}Q${user.is_qp}
        </span>
        `;
    const uplineInfo =
        user.spread_uid > 0
            ? `<a href="https://totemlife.cn/apps/crm/disclose-user-info?uid=${user.spread_uid}" class="custom-link">
            ${user.cpromoter == 1 ? '<i class="fa fa-rss"></i>' : ""}
            上级推荐人C#${user.spread_uid} 
        </a>`
            : '<span style="color:#c1c1c1">无上级推荐人</span>';
    const repInfo =
        user.rep_id > 0
            ? `<span sytle="color:#c1c1c1">服务代表R#${user.rep_id}[${user.rep_code}]</span>`
            : '<span style="color:#c1c1c1">未分配代表</span>';
    const csrInfo =
        user.cs_aid > 0
            ? `<span sytle="color:#c1c1c1">客服专员A#${user.cs_aid}</span>`
            : '<span style="color:#c1c1c1">无客服专员</span>';

    user = {
        ...user,
        discount,
        avatar,
        memberInfo,
        subscribeInfo,
        promoterInfo,
        typeInfo,
        roleInfo,
        codes,
        uplineInfo,
        repInfo,
        csrInfo,
    };

    return user;
}

async function getTask(arg) {
    const isInteger = Number.isInteger(arg);

    const condition = isInteger ? `id = ${arg}` : `id in ( ${arg} )`;
    const query = `select * from tutem_mall.tasks WHERE ${condition}`;
    const [tasks] = await rds.query(query);
    return tasks[0];
}

async function createSalesLead(leadData) {
    const sql = `
        INSERT INTO sales_leads (
            creator_uid, follower_uid, stage, contact, organization, title, phone, wechat, email, source, remark, attachments, sales_forecast, forecast_unit, related_clients, priority
        ) VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )
    `;
    const values = [
        leadData.creator_uid,
        leadData.follower_uid,
        leadData.stage,
        leadData.contact,
        leadData.organization,
        leadData.title,
        leadData.phone,
        leadData.wechat,
        leadData.email,
        leadData.source,
        leadData.remark,
        JSON.stringify(leadData.attachments),
        leadData.sales_forecast,
        leadData.forecast_unit,
        leadData.related_clients,
        leadData.priority,
    ];
    const [rows] = await rds.query(sql, values);
    return rows.insertId;
}

async function updateSalesLead(leadId, leadData) {
    const sql = `
        UPDATE sales_leads SET
            follower_uid = ?, stage = ?, contact = ?, organization = ?, title = ?, phone = ?, wechat = ?, email = ?,
            source = ?, remark = ?, attachments = ?, sales_forecast = ?, forecast_unit = ?, related_customer_uid = ?, update_time = NOW()
        WHERE id = ?
    `;
    const values = [
        leadData.follower_uid,
        leadData.stage,
        leadData.contact,
        leadData.organization,
        leadData.title,
        leadData.phone,
        leadData.wechat,
        leadData.email,
        leadData.source,
        leadData.remark,
        JSON.stringify(leadData.attachments),
        leadData.sales_forecast,
        leadData.forecast_unit,
        leadData.related_customer_uid,
        leadId,
    ];
    await rds.query(sql, values);
}

async function getSalesLeadById(leadId) {
    const sql = `
        SELECT * FROM sales_leads WHERE id = ?
    `;
    const [rows] = await rds.query(sql, [leadId]);
    return rows[0];
}

async function getSalesLeadByCreatorOrFollower(uid) {
    const sql = `
        SELECT * FROM sales_leads WHERE creator_uid = ? OR follower_uid = ?
    `;
    const [rows] = await rds.query(sql, [uid, uid]);
    return rows;
}

// eb_user_profile表的公用用户档案查询函数
async function querySalesLeads(query, params = []) {
    const [rows] = await rds.query(query, params);
    return rows.map((row) => {
        let { attachments } = row;
        let urls = [];
        if (attachments.startsWith("[") && attachments.endsWith("]")) {
            try {
                urls = JSON.parse(attachments);
            } catch (error) {
                urls = []; // 如果 JSON 解析出错，将 urls 设置为空数组
            }
        }
        const attachments_link =
            urls && urls.length > 0
                ? urls
                      .map(
                          (url, index) =>
                              `<a href="${url}" target="_blank">附件-${index + 1}</a> `
                      )
                      .join("")
                : "";
        const lead = { ...row, attachments_link };
        return lead;
    });
}

// 保存操作历史
async function saveSalesLeadHistory(data) {
    const {
        salesLeadId,
        operationType,
        operatorUid,
        operationTime,
        oldData,
        newData,
    } = data;
    // 保存到数据库
    await db.query(
        ` INSERT INTO sales_lead_history ( sales_lead_id, operation_type, operator_uid, operation_time, old_data, new_data) VALUES (?, ?, ?, ?, ?, ?) `,
        [
            salesLeadId,
            operationType,
            operatorUid,
            operationTime,
            JSON.stringify(oldData),
            JSON.stringify(newData),
        ]
    );
}

const dbq = {
    getUser,
    getTask,
    queryUserProfiles,
    checkUserPermission,
    checkOrderPermission,
    checkTaskPermission,
    createSalesLead,
    updateSalesLead,
    getSalesLeadById,
    getSalesLeadByCreatorOrFollower,
    querySalesLeads,
};

module.exports = dbq;
